package dao.impl.BankDaoImpl;

import common.model.Entity.bankEntity.Account;
import common.util.DBUtil;
import dao.BankDao.AccountDao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import java.math.BigDecimal;

/**
 * {@code AccountDaoImpl} 实现了 {@link AccountDao} 接口，
 * 提供对 {@link Account} 实体的数据库访问操作。
 * <p>
 * 包括账户的新增、查询、更新、逻辑删除等功能，使用 JDBC 与数据库交互。
 * </p>
 */
public class AccountDaoImpl implements AccountDao {

    /**
     * 根据用户 ID 查询该用户的所有账户。
     *
     * @param userId 用户 ID
     * @return 账户列表
     */
    @Override
    public List<Account> findAccountsByUserId(Long userId) {
        List<Account> accounts = new ArrayList<>();
        String sql = "SELECT id, user_id, account_name, balance, max_deposit_limit, status, created_at, updated_at " +
                "FROM t_bank_account WHERE user_id = ? AND is_deleted = 0";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setLong(1, userId);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Account account = new Account();
                    account.setId(rs.getLong("id"));
                    account.setUserId(rs.getLong("user_id"));
                    account.setAccountName(rs.getString("account_name"));
                    account.setBalance(rs.getBigDecimal("balance"));
                    account.setLimit(rs.getBigDecimal("max_deposit_limit"));
                    account.setStatus(rs.getInt("status"));
                    account.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
                    account.setUpdatedAt(rs.getTimestamp("updated_at").toLocalDateTime());
                    accounts.add(account);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return accounts;
    }

    /**
     * 根据账户 ID 查询账户。
     *
     * @param accountId 账户 ID
     * @return 对应的账户对象，可能为 {@code null}
     */
    @Override
    public Account findAccountById(Long accountId) {
        Account account = null;
        String sql = "SELECT id, user_id, account_name, balance, max_deposit_limit, status, created_at, updated_at " +
                "FROM t_bank_account WHERE id = ? AND is_deleted = 0";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setLong(1, accountId);

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    account = new Account();
                    account.setId(rs.getLong("id"));
                    account.setUserId(rs.getLong("user_id"));
                    account.setAccountName(rs.getString("account_name"));
                    account.setBalance(rs.getBigDecimal("balance"));
                    account.setLimit(rs.getBigDecimal("max_deposit_limit"));
                    account.setStatus(rs.getInt("status"));
                    account.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
                    account.setUpdatedAt(rs.getTimestamp("updated_at").toLocalDateTime());
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return account;
    }

    /**
     * 更新指定账户的余额。
     *
     * @param accountId  账户 ID
     * @param newBalance 新余额
     * @return 是否更新成功
     */
    @Override
    public boolean updateBalance(Long accountId, BigDecimal newBalance) {
        String sql = "UPDATE t_bank_account SET balance = ?, updated_at = NOW() WHERE id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setBigDecimal(1, newBalance);
            ps.setLong(2, accountId);
            int rows = ps.executeUpdate();
            return rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 根据账户 ID 查找账户。
     *
     * @param aLong 账户 ID
     * @return 封装在 {@link Optional} 中的账户对象
     */
    @Override
    public Optional<Account> findById(Long aLong) {
        Account account = findAccountById(aLong);
        return Optional.ofNullable(account);
    }

    /**
     * 查询所有未删除的账户。
     *
     * @return 账户列表
     */
    @Override
    public List<Account> findAll() {
        List<Account> accounts = new ArrayList<>();
        String sql = "SELECT id, user_id, balance, max_deposit_limit, status, created_at, updated_at " +
                "FROM t_bank_account WHERE is_deleted = 0";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            while (rs.next()) {
                Account account = new Account();
                account.setId(rs.getLong("id"));
                account.setUserId(rs.getLong("user_id"));
                account.setBalance(rs.getBigDecimal("balance"));
                account.setLimit(rs.getBigDecimal("max_deposit_limit"));
                account.setStatus(rs.getInt("status"));
                account.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
                account.setUpdatedAt(rs.getTimestamp("updated_at").toLocalDateTime());
                accounts.add(account);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return accounts;
    }

    /**
     * 保存一个新账户。
     *
     * @param entity 账户实体
     * @return 是否保存成功
     */
    @Override
    public boolean save(Account entity) {
        String sql = "INSERT INTO t_bank_account " +
                "(user_id, account_name, balance, max_deposit_limit, status, created_at, updated_at) " +
                "VALUES (?, ?, ?, ?, ?, NOW(), NOW())";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            BigDecimal limit = entity.getLimit();
            if (limit == null) {
                limit = new BigDecimal("50000.00"); // 默认值
                entity.setLimit(limit);
            }

            if (entity.getAccountName() == null) {
                entity.setAccountName("新账户");
            }

            ps.setLong(1, entity.getUserId());
            ps.setString(2, entity.getAccountName());
            ps.setBigDecimal(3, entity.getBalance() == null ? BigDecimal.ZERO : entity.getBalance());
            ps.setBigDecimal(4, entity.getLimit());
            ps.setInt(5, entity.getStatus());

            int rows = ps.executeUpdate();
            if (rows > 0) {
                try (ResultSet rs = ps.getGeneratedKeys()) {
                    if (rs.next()) {
                        entity.setId(rs.getLong(1)); // 回填主键 ID
                    }
                }
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 更新账户信息。
     *
     * @param entity 账户实体
     * @return 是否更新成功
     */
    @Override
    public boolean update(Account entity) {
        String sql = "UPDATE t_bank_account SET user_id = ?, balance = ?, max_deposit_limit = ?, status = ?, updated_at = NOW() WHERE id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setLong(1, entity.getUserId());
            ps.setBigDecimal(2, entity.getBalance());
            ps.setBigDecimal(3, entity.getLimit());
            ps.setInt(4, entity.getStatus());
            ps.setLong(5, entity.getId());

            int rows = ps.executeUpdate();
            return rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 根据账户 ID 执行逻辑删除。
     *
     * @param aLong 账户 ID
     * @return 是否删除成功
     */
    @Override
    public boolean deleteById(Long aLong) {
        String sql = "UPDATE t_bank_account SET is_deleted = 1, updated_at = NOW() WHERE id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setLong(1, aLong);

            int rows = ps.executeUpdate();
            return rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 更新账户名称。
     *
     * @param accountId 账户 ID
     * @param newName   新名称
     * @return 是否更新成功
     */
    @Override
    public boolean updateAccountName(Long accountId, String newName) {
        String sql = "UPDATE t_bank_account SET account_name = ?, updated_at = NOW() WHERE id = ? AND is_deleted = 0";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, newName);
            ps.setLong(2, accountId);
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 根据账户 ID 执行逻辑删除。
     *
     * @param accountId 账户 ID
     * @return 是否删除成功
     */
    @Override
    public boolean deleteAccount(Long accountId) {
        String sql = "UPDATE t_bank_account SET is_deleted = 1, updated_at = NOW() WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setLong(1, accountId);
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 统计未删除的账户数量。
     *
     * @return 账户数量
     */
    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM t_bank_account WHERE is_deleted = 0";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
